# -*- coding: utf-8 -*-
#人员证书别名为匹配列表
import json


def getPersonCertUnMatchList(request):
    #1数据
    request.cursor_standard.execute(request.formatSql('''
        select distinct 
            person_apt_un_match.cert_type,
            person_apt_un_match.profession,
            person_apt_un_match.person_type,
            person_apt_un_match.data_source_code   data_source
        from middle.person_apt_un_match  
        where data_source_code = '${data_source}'
        and (
            cert_type like '%${search_key}%'
            or profession like '%${search_key}%'
            or person_type like '%${search_key}%'
        )
        order by data_source_code,cert_type,profession,person_type 

        limit ${start} , ${rows}
    '''))
    rows = request.cursor_standard.fetchall()
    #2页码
    request.cursor_standard.execute(request.formatSql('''
        select count(*) as total 
            from (
                select distinct 
                    person_apt_un_match.cert_type,
                    person_apt_un_match.profession,
                    person_apt_un_match.person_type,
                    person_apt_un_match.data_source_code  
                from middle.person_apt_un_match  
                where data_source_code = '${data_source}'
                and (
                    cert_type like '%${search_key}%'
                    or profession like '%${search_key}%'
                    or person_type like '%${search_key}%'
                )
            ) as a
    '''))
    total = request.cursor_standard.fetchone()['total']
    return json.dumps({'rows':rows,'total':total}, ensure_ascii=False)